package columbia.edu;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
 * Servlet implementation class Display
 */
public class Display extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public Display() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		//int[] price;
		//int i=0;
		//price = new int[100];  
		
        ArrayList<String> prodArray = new ArrayList<String>(); 

		String connectionURL = "jdbc:oracle:thin:@//w4111b.cs.columbia.edu:1521/ADB";
		Connection connection=null;
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		String str = request.getParameter("name");
		System.out.println("Str="+str);
		System.out.println("Logged in="+request.getSession().getAttribute("theName"));
		String item=null;

		ResultSet rs;
		try {
			String sql=null;
			Class.forName("oracle.jdbc.pool.OracleDataSource");
		      //connection = DriverManager.getConnection(connectionURL, "ab3416", "kFxJssQk");
			connection = (Connection) request.getSession().getAttribute("Connection");	
			if (connection == null) {
				connection = DriverManager.getConnection(connectionURL, "ab3416", "kFxJssQk");
				request.getSession().setAttribute("Connection",connection);
			}
			if (str.equals("tops")){
					sql = "select p.description,c.brand,p.price,p.pid from products p, clothes c where c.category='T' and p.pid=c.pid ";
				}else if (str.equals("pants")){
					sql = "select p.description,c.brand,p.price,p.pid from products p, clothes c where c.category='P' and p.pid=c.pid ";
				}else if (str.equals("other")){
					sql = "select p.description,c.brand,p.price,p.pid from products p, clothes c where c.category='O' and p.pid=c.pid ";
				}else if (str.equals("books")){
					sql = "select p.description,b.author,p.price,p.pid from products p, books b where p.pid=b.pid ";
				}
		    Statement s = connection.createStatement();
		    s.executeQuery (sql);
			rs = s.getResultSet();
			if(str.equals("tops")||str.equals("pants")||str.equals("other")){

			while(rs.next()){

	            String str1 = rs.getString("description");
	            prodArray.add(str1); 
	            String str2 = rs.getString("brand");
	            prodArray.add(str2); 
	            String str3 = rs.getString("price");
	            prodArray.add(str3); 
				String str4 = rs.getString("pid");
	            prodArray.add(str4);
	            item="clothes";
	            //price[i]=rs.getInt("price");
	            
	            System.out.println(str1);
	            System.out.println(str2);
	            System.out.println(str3);
	            System.out.println(str4);
	            //i++;
			}
			}else if(str.equals("books")){
				while(rs.next()){
				
				String str1 = rs.getString("description");
	            prodArray.add(str1); 
	            String str2 = rs.getString("author");
	            prodArray.add(str2); 
	            String str3 = rs.getString("price");
	            prodArray.add(str3); 
				String str4 = rs.getString("pid");
	            prodArray.add(str4);
	            item="books";

				} 
				
			}
		}
		catch(ClassNotFoundException e){
			out.println("Couldn't load database driver: " + e.getMessage());
		}
		catch(SQLException e){
			out.println("SQLException caught: " + e.getMessage());
		}
		catch (Exception e){
			out.println(e);
		}
		finally {
				//request.setAttribute("array",price );
				//request.setAttribute("length",i );
				request.setAttribute("pricearray",prodArray);
				request.setAttribute("item",item);

				String nextJSP = "/Display.jsp";
				RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(nextJSP);
				dispatcher.forward(request,response);
/*
				try {
					if (connection != null) connection.close();
				}
				catch (SQLException ignored){
					out.println(ignored);
				}
*/		}
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
	}

}
